import connector.DatabaseDriver
from connector.DatabaseDriver import DatabaseDriver


def search_book(driver: DatabaseDriver, text: str, book_id: int = None):
    # book_id
    if book_id is None:
        # 说明现在是文本查询
        results: tuple = driver.select_all('select * from book where book_name like "%{}%" or author like "%{}%" or publisher like "%{}%"'.format(text, text, text))
        return results
    else:
        # 现在是根据书本号查询
        results: tuple = driver.select_all('select * from book where book_id={}'.format(book_id))
        return results


def get_all_books(driver: DatabaseDriver) -> tuple:
    results: tuple = driver.select_all('select * from book')
    return results


# 根据用户id查询是否有待还的书
def get_borrow_book_by_user_id(driver: DatabaseDriver, user_id: int):
    # select * from borrow where user_id ={} and return_time is Null
    if user_id is not None:
        # return driver.select_all("select * from borrow where user_id ={} and return_time is Null".format(user_id))
        # select borrow.*,book.book_name,book.author,book,save_loc from book,borrow where
        # book.book_id=borrow.book_id and borrow.user_id=1 and return_time is NULL
        return driver.select_all("select borrow.*, book.book_name ,book.author, book.save_loc "
                                 "from book,borrow where book.book_id = borrow.book_id and borrow.user_id = {} and "
                                 "return_time is NULL".format(user_id))


def get_book_by_name_author_type(driver: DatabaseDriver, book_name: str = '', author: str = '', book_type: int = None) -> tuple:
    if book_name == '' and author == '' and book_type == -1:
        return get_all_books(driver)
    else:
        if book_type == -1:
            book_type = None
        sql: str = ''
        #
        if book_name != '' and author != '' and book_type is not None:
            sql: str = 'select * from book where book_name like "%{}%" and author like "%{}%" and cate_id={}'.format(book_name, author, book_type)
        elif book_name != '' and author != '':
            sql: str = 'select * from book where book_name like "%{}%" and author like "%{}%"'.format(book_name, author)
        elif book_name != '' and book_type is not None:
            sql: str = 'select * from book where book_name like "%{}%" and cate_id={}'.format(book_name, book_type)
        elif author != '' and book_type is not None:
            sql: str = 'select * from book where  author like "%{}%" and cate_id={}'.format(author, book_type)
        elif book_name != '':
            sql: str = 'select * from book where book_name like "%{}%"'.format(book_name)
        elif author != '':
            sql: str = 'select * from book where author like "%{}%"'.format(author)
        elif book_type != '':
            sql: str = 'select * from book where cate_id={}'.format(book_type)
        return driver.select_all(sql=sql)


def user_search_books_by_book_id(driver: DatabaseDriver, book_id: int):
    results: tuple = driver.select_all('select * from book where book_id={}'.format(book_id))
    return results


# 修改书本
def update_book(driver: DatabaseDriver, book_name: str, book_publisher: str, book_author: str, cate_id: int, nums: int,
                save_loc: str) -> int:
    return driver.update(
        'update book set book_name="{}",publisher="{}",author="{}",cate_id={},nums={},sava_loc="{}" where book_id={}'.format(
            book_name, book_publisher, book_author, cate_id, nums, save_loc))


# 插入新书本
def insert_book(driver: DatabaseDriver, book_name: str, book_publisher: str, book_author: str, cate_id: int, nums: int,
                save_loc: str) -> int:
    return driver.insert(
        'insert into book(book_name,publisher, author, cate_id, nums, save_loc) values ("{}","{}","{}",{},{},"{}")'.format(
            book_name, book_publisher, book_author, cate_id, nums, save_loc))


# 删除书本
def delete_book(driver: DatabaseDriver, book_id: int) -> int:
    return driver.delete('delete from book where book_id={}'.format(book_id))